﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BLL;
using Model;
using MDP.WebOpenPortal.Models;
using System.Data;
using System.Data.SqlClient;
using Common;
using System.Web.Script.Serialization;
using System.Text;

namespace MDP.WebOpenPortal.Controllers
{
    public class EtlTargetController : BaseController
    {
        private static EncrypCode encrypCode = new EncrypCode();

        public ActionResult Index(FormCollection collection)
        {
            #region 检查Url参数
            var urlRouteInfo = new Model.UrlRoute();
            try
            {
                urlRouteInfo = CheckUrlRoute("EtlTarget");
            }
            catch (Exception ex)
            {
                Response.Write("操作错误！" + ex.Message);
                Response.End();
                return View();
            }
            #endregion

            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = urlRouteInfo.DBName;
            EtlTargetSystemBLL targetBLL = new EtlTargetSystemBLL(loginInfos);
            EtlSourceSystemBLL sourceBLL = new EtlSourceSystemBLL(loginInfos);

            var systemID = urlRouteInfo.EntityId;
            var targetInfo = targetBLL.GetInfoById(systemID);
            ETLTargetModel sourceModel = new ETLTargetModel();
            sourceModel.EtlTargetInfo = targetInfo;
            sourceModel.DbName = urlRouteInfo.DBName;

            string strContactList = string.Empty;
            IList<SysUserInfo> contactList = sourceBLL.GetEtlSourceUserList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            foreach (var contactItem in contactList)
            {
                if (urlRouteInfo.UserID == contactItem.Username)
                {
                    Session["MDP_UN_Open"] = contactItem.EmployeeName;
                }
                var userInfo = "工号:" + contactItem.EmployeeCode + "\n用户名:" + contactItem.Username + "\n公司:" + contactItem.CompanyName + "\n电话:" + contactItem.OfficeTel + "\nEmail:" + contactItem.OfficeEmail;
                strContactList += "<span class='s3 s3" + contactItem.Guid + "'>  <!--<input type='checkbox' value='" + contactItem.Username + "' />--><a title='" + userInfo + "'>" + contactItem.EmployeeName + "</a>  </span>";
            }
            sourceModel.StrContactList = strContactList;

            DictionaryBLL dictionaryBLL = new DictionaryBLL(loginInfos);
            var dictionaryList = dictionaryBLL.GetListByKeyName("SourceType");
            foreach (var dictionaryItem in dictionaryList)
            {
                sourceModel.EtlTargetTypes.Add(new SelectListItem()
                {
                    Text = dictionaryItem.Name,
                    Value = dictionaryItem.Value,
                    Selected = dictionaryItem.Value.Equals(targetInfo.TargetType.ToString()) ? true : false
                });
            }
            return View(sourceModel);
        }

        /// <summary>
        /// 取源系统连接字符串
        /// </summary>
        /// <param name="systemID"></param>
        /// <returns></returns>
        public JsonResult GetTargetDbConfigList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            systemID = string.IsNullOrEmpty(systemID) ? "" : systemID;
            //IList<ETLDbConfig> list = bll.GetETLDbConfigList(new IDataParameter[] { new SqlParameter("@SysID", targetID) });
            IList<ETLDbConfig> list = bll.GetDbConfigBySysID(systemID);
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            List<ETLDbInfo> dbinfo = new List<ETLDbInfo>();
            foreach (ETLDbConfig db in list)
            {
                ETLDbInfo info = new ETLDbInfo()
                {
                    GUID = db.GUID,
                    Host = db.DbDataSource,
                    Server = db.DbDatabase,
                    User = db.DbUserID,
                    Pass = encrypCode.Decrypto(db.DbPassword),
                    Part = db.DbPort.ToString(),
                    SID = db.DbSID,
                    FileUrl = db.DbDataSource,
                    FileUser = db.DbUserID,
                    FilePass = encrypCode.Decrypto(db.DbPassword)
                };
                dbinfo.Add(info);
            }
            jsonDict.Add("total", dbinfo.Count);
            jsonDict.Add("rows", dbinfo);
            return Json(jsonDict);
        }

        /// <summary>
        /// 更新对接系统
        /// </summary>
        /// <param name="model">提交的系统信息</param>
        /// <returns></returns>
        public ActionResult EtlTargetUpdate(EtlTargetSystemInfo model, FormCollection collection, string dbName)
        {
            JsonResponse<string> jsonResponse = new JsonResponse<string>();
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            int resultCount = 0;
            EtlTargetSystemBLL targetSystemBLL = new EtlTargetSystemBLL(loginInfos);
            EtlSourceSystemBLL sourcesystemBll = new EtlSourceSystemBLL(loginInfos);

            var connList = GetDatabaseConnections(collection);
            //var contacts = sourcesystemBll.GetEtlSourceUserList(new IDataParameter[] { new SqlParameter("@SysID", model.GUID) }); ;

            if (string.IsNullOrEmpty(model.TargetDBCode))
                model.TargetDBCode = "";
            if (connList.Count > 0)
                model.Connstr = connList[0].DbConnectionStr;

            if (!string.IsNullOrEmpty(model.GUID))//更新信息
            {
                var targetInfo = targetSystemBLL.GetInfoById(model.GUID);
                model.TargetSysCode = targetInfo.TargetSysCode;
                model.TargetSysName = targetInfo.TargetSysName;
                resultCount = targetSystemBLL.Update2(model);
                //if (resultCount == 1)
                //{
                //    new SysOperationLog(loginInfos).Log("对接系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功, "更新对接系统信息操作,操作成功,ID=" + model.GUID, Request.Cookies["MDM_UserName"].Value);
                //}
            } 
            if (resultCount == 1)
            {  

                #region 连接字符串
                IList<ETLDbConfig> oldDbConfigList = sourcesystemBll.GetDbConfigBySysID(model.GUID);
                int dbSortID = 1;
                for (int i = 0; i < oldDbConfigList.Count; i++)
                {
                    var oldDbConfigInfo = oldDbConfigList[i];
                    if (i < connList.Count)
                    {
                        var dbConfigInfo = connList[i];
                        oldDbConfigInfo.DbSortID = dbSortID++;
                        oldDbConfigInfo.DbDataSource = dbConfigInfo.DbDataSource;
                        oldDbConfigInfo.DbDatabase = dbConfigInfo.DbDatabase;
                        oldDbConfigInfo.DbUserID = dbConfigInfo.DbUserID;
                        oldDbConfigInfo.DbPassword = dbConfigInfo.DbPassword;
                        oldDbConfigInfo.DbPort = dbConfigInfo.DbPort;
                        oldDbConfigInfo.DbSID = dbConfigInfo.DbSID;
                        oldDbConfigInfo.DbConnectionStr = dbConfigInfo.DbConnectionStr;
                        oldDbConfigInfo.UpdateUser = loginInfos.Dbuser;
                        // 更新
                        sourcesystemBll.UpDateDbConfig(oldDbConfigInfo);
                    }
                    else
                    {
                        // 删除:多余的删除
                        oldDbConfigInfo.DeleteUser = loginInfos.Dbuser;
                        sourcesystemBll.DeleteDbConfig(oldDbConfigInfo);
                    }
                }
                for (int i = oldDbConfigList.Count; i < connList.Count; i++)
                {
                    var dbConfigInfo = connList[i];
                    dbConfigInfo.GUID = Guid.NewGuid().ToString();
                    dbConfigInfo.DbSortID = dbSortID++;
                    dbConfigInfo.SysID = model.GUID;
                    dbConfigInfo.DbType = model.TargetType;
                    dbConfigInfo.CreateUser = model.CreateUser ?? loginInfos.Dbuser;
                    sourcesystemBll.AddDbConfig(dbConfigInfo);
                }

                #endregion

                 
            }
            if (resultCount == 1)
            {
                jsonResponse.Success = true;
                jsonResponse.Message = "保存成功";
                jsonResponse.Data = "OK";
            }
            else if (resultCount == 2)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "系统名称已存在!请使用其他系统名称。";
                jsonResponse.Data = resultCount.ToString();
            }
            else if (resultCount == 3)
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "由系统名称创建的数据库登录名已存在!请使用其他系统名称。";
                jsonResponse.Data = resultCount.ToString();
            }
            else
            {
                jsonResponse.Success = false;
                jsonResponse.Message = "保存失败";
                jsonResponse.Data = resultCount.ToString();
            }
            return Json(jsonResponse);
        }

        private List<ETLDbConfig> GetDatabaseConnections(FormCollection collection)//, int sourceType)
        {
            List<ETLDbConfig> list = new List<ETLDbConfig>();
            var sourceType = Convert.ToInt32(collection["TargetType"]);

            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            ETLDbInfo dbInfo = (ETLDbInfo)jsSerializer.Deserialize(collection["DBs"], typeof(ETLDbInfo));

            var dataSource = dbInfo.Host;
            var database = dbInfo.Server;
            var userID = dbInfo.User;
            var password = dbInfo.Pass;
            string port = dbInfo.Part ;
            var sid = dbInfo.SID;
            var strCon = string.Empty;

            switch (sourceType)
            {
                case 1:
                    strCon = string.Format(DatabaseConnStr.SQLSERVER_FRAMAT, dataSource, database, userID, password, port, sid);
                    break;
                case 2:
                   strCon = string.Format(DatabaseConnStr.ORACLE_FRAMAT, dataSource, database, userID, password, port, sid);
                    if (dataSource.IndexOf("|") > -1)
                    {
                        List<string> hostArr = dataSource.Split('|').ToList();
                        List<string> portArr = dbInfo.Part.Split('|').ToList();
                        StringBuilder ora = new StringBuilder();
                        ora.Append("Data Source=(DESCRIPTION=");
                        for (int i = 0; i < hostArr.Count; i++)
                        {
                            ora.Append(string.Format("(ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1}))", hostArr[i], portArr[i]));
                        }
                        ora.Append(string.Format("(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {0})));User Id={1};Password={2};", database, userID, password));
                        strCon = ora.ToString();
                    }
                    break;
                case 4:
                case 8:
                    strCon = string.Format(DatabaseConnStr.MYSQL_FRAMAT, dataSource, database, userID, password, port, sid);
                    break;
                case 3:
                case 5:
                case 6:
                    dataSource = dbInfo.FileUrl;
                    userID = dbInfo.FileUser;
                    password = dbInfo.FilePass;
                    strCon = string.Format(DatabaseConnStr.ACCESS_FRAMAT, dbInfo.FileUrl, dbInfo.FileUser, dbInfo.FilePass);
                    break;
            }
            list.Add(new ETLDbConfig()
            {

                DbDataSource = dataSource,
                DbDatabase = database,
                DbUserID = userID,
                DbPassword = encrypCode.Encrypto(password),
                DbPort = port,
                DbSID = sid,
                DbConnectionStr = encrypCode.Encrypto(strCon)
            });
            return list;
        }

    }
}
